/*
 * BancoDAO.java
 *
 * Created on 9 de Agosto de 2006, 14:13
 *
 * To change this template, choose Tools | Template Manager
 * and open the template in the editor.
 */

package br.cefetrn.banco;

import java.awt.HeadlessException;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;
import java.util.GregorianCalendar;

import javax.swing.JOptionPane;

import utilidades.FormatarData;

import br.cefetrn.dominio.Declaracao;
import br.cefetrn.dominio.Responsavel_Declaracao;
import br.cefetrn.fachada.FachadaDeclaracao;
import br.cefetrn.fachada.FachadaResponsavel_Declaracao;

/**
 *
 * @author leomoreira
 */
public class DeclaracaoDAO implements FachadaDeclaracao {
	
	FachadaResponsavel_Declaracao f = new Responsavel_DeclaracaoDAO();
    
   @SuppressWarnings("finally")
   public int insertDeclaracaoPeq(Declaracao d) throws ClassNotFoundException, IOException {
        
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        int cont = 0;
        
        try {
            conn = Banco.getConexao();
            stmt = conn.prepareStatement("insert into declaracao (cpfcnpj, nome, ano) values (?, ?, ?)");
            stmt.setString(1, d.getCpfcnpj());
            stmt.setString(2, d.getNome().trim());
            stmt.setString(3, d.getAno().trim());
            stmt.executeUpdate();
            cont = 1;
        }
        catch (SQLException ex) {
            cont = 0;
            ex.printStackTrace();
        }
        finally {
	        Banco.closeConexao(conn, rs, stmt);
	        return cont;
        }
    }

   @SuppressWarnings("finally")
public int updateDeclaracao(Declaracao d, Responsavel_Declaracao res) throws ClassNotFoundException, IOException {
       
       Connection conn = null;
       PreparedStatement stmt = null;
       ResultSet rs = null;
       FachadaResponsavel_Declaracao fac = new Responsavel_DeclaracaoDAO();
       int cont = 0;
       
       try {
           conn = Banco.getConexao();
           stmt = conn.prepareStatement("update declaracao set nome = ?, ano = ?, natureza = ?, administrador = ?, " +
           		"instituicao = ?, cpf_responsavel = ?, retificadora = ?, extincao = ?, data_evento = ? " +
           		"where cpfcnpj = ? ");
           stmt.setString(1, d.getNome().trim());
           stmt.setString(2, d.getAno());
           stmt.setString(3, d.getNatureza());
           stmt.setBoolean(4, d.isAdministrador());
           stmt.setBoolean(5, d.isInstituicao());
           stmt.setString(6, d.getCpf_responsavel());
           stmt.setBoolean(7, d.isRetificadora());
           stmt.setBoolean(8, d.isExtincao());
           stmt.setString(9, FormatarData.formatar(d.getData_evento()));
           stmt.setString(10, d.getCpfcnpj());
           stmt.executeUpdate();
           fac.insertResponsavel_DeclaracaoP(res);
           insertDeclaracao_Responsavel(d.getCpfcnpj(), res.getCpf());
           cont = 1;
       } catch (SQLException ex) {
    	   ex.printStackTrace();
           cont = 0;
       } finally {
           Banco.closeConexao(conn, rs, stmt);
           return cont;
       }
   }
    
   	public void insertDeclaracao_Responsavel(String cpfcnpj, String cpf) throws ClassNotFoundException, IOException {
   		
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        
        try {
            conn = Banco.getConexao();
            stmt = conn.prepareStatement("insert into declaracao_responsavel (id_declaracao, id_responsavel_declaracao) " +
            		"values (?, ?)");
            stmt.setString(1, cpfcnpj);
            stmt.setString(2, cpf);
            stmt.executeUpdate();
        }
        catch (SQLException ex) {
            ex.printStackTrace();
        }
        finally {
	        Banco.closeConexao(conn, rs, stmt);
        }   		
   	}
   
    public Declaracao selectDeclaracao(String cpfcnpj) throws ClassNotFoundException, IOException {
        
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        Declaracao d = null;
        Responsavel_Declaracao resp;
        FachadaResponsavel_Declaracao f = new Responsavel_DeclaracaoDAO();
        
        try {
            conn = Banco.getConexao();
            stmt = conn.prepareStatement("select * from declaracao where cpfcnpj = ?");
            stmt.setString(1, cpfcnpj);
            rs = stmt.executeQuery();
            while (rs.next()){
                d = new Declaracao();
                resp = new Responsavel_Declaracao();
                d.setCpfcnpj(rs.getString("cpfcnpj"));
                d.setNome(rs.getString("nome"));
                d.setAno(rs.getString("ano"));
                d.setNatureza(rs.getString("natureza"));
                d.setAdministrador(rs.getBoolean("administrador"));
                d.setInstituicao(rs.getBoolean("instituicao"));
                d.setCpf_responsavel(rs.getString("cpf_responsavel"));
                d.setRetificadora(rs.getBoolean("retificadora"));
                d.setExtincao(rs.getBoolean("extincao"));
                d.setData_evento(FormatarData.inserirData(rs.getString("data_evento")));
                resp = f.selectResp_Declaracao(rs.getString("cpf_responsavel_declaracao"));
                d.setResponsavel(resp);
            }
        }
        catch (SQLException ex) {
        }
        finally {
	        Banco.closeConexao(conn, rs, stmt);
	        return d;
        }
    }
    
    public Collection selectTodasDeclaracoes() throws ClassNotFoundException, IOException {
        
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        Collection res = new ArrayList();
        Declaracao d;
        Responsavel_Declaracao resp;
        
        try {
            conn = Banco.getConexao();
            stmt = conn.prepareStatement("select * from declaracao");
            rs = stmt.executeQuery();
            while (rs.next()){
                d = new Declaracao();
                resp = new Responsavel_Declaracao();
                d.setCpfcnpj(rs.getString("cpfcnpj"));
                d.setNome(rs.getString("nome"));
                d.setAno(rs.getString("ano"));
                d.setNatureza(rs.getString("natureza"));
                d.setAdministrador(rs.getBoolean("administrador"));
                d.setInstituicao(rs.getBoolean("instituicao"));
                d.setCpf_responsavel(rs.getString("cpf_responsavel"));
                d.setRetificadora(rs.getBoolean("retificadora"));
                d.setExtincao(rs.getBoolean("extincao"));
                d.setData_evento(FormatarData.inserirData(rs.getString("data_evento")));
                resp = selectResponsavel(d.getCpfcnpj());
                d.setResponsavel(resp);
                res.add(d);
            }
        }
        catch (SQLException ex) {
        	ex.printStackTrace();
        }
        finally {
	        Banco.closeConexao(conn, rs, stmt);
	        return res;
        }
    }
    
    public Responsavel_Declaracao selectResponsavel(String cpfcnpj) throws ClassNotFoundException, IOException {
        
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        Responsavel_Declaracao resp = null;
        FachadaResponsavel_Declaracao f = new Responsavel_DeclaracaoDAO();
        
        try {
            conn = Banco.getConexao();
            stmt = conn.prepareStatement("select cpf, nome, ddd, tel, ramal, fax, email from declaracao_responsavel" +
            		" inner join responsavel_declaracao on (declaracao_responsavel.id_responsavel_declaracao = responsavel_declaracao.cpf)" +
            		" where declaracao_responsavel.id_declaracao = ?");
            stmt.setString(1, cpfcnpj);
            rs = stmt.executeQuery();
            while (rs.next()){
            	resp = new Responsavel_Declaracao();
                resp.setCpf(rs.getString("cpf"));
                resp.setDdd(rs.getInt("ddd"));
                resp.setEmail(rs.getString("email"));
                resp.setFax(rs.getInt("fax"));
                resp.setNome(rs.getString("nome"));
                resp.setRamal(rs.getInt("ramal"));
                resp.setTel(rs.getInt("tel"));
            }
        }
        catch (SQLException ex) {
        	ex.printStackTrace();
        }
        finally {
	        Banco.closeConexao(conn, rs, stmt);
	        return resp;
        }
    }    
    
    @SuppressWarnings({ "finally", "unchecked" })
	public Collection selectAno() throws ClassNotFoundException, IOException {
        
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        Collection<String> res = new ArrayList<String>();
        
        try {
            conn = Banco.getConexao();
            stmt = conn.prepareStatement("select (ano) from ano_calendario");
            rs = stmt.executeQuery();
            while (rs.next()){
                res.add(rs.getString("ano"));
            }
        }
        catch (SQLException ex) {
        	ex.printStackTrace();
        }
        finally {
        Banco.closeConexao(conn, rs, stmt);
        return res;
        }
    }
    
    @SuppressWarnings("finally")
	public Collection selectNatureza() throws ClassNotFoundException, IOException {
        
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        Collection<String> res = new ArrayList<String>();
        
        try {
            conn = Banco.getConexao();
            stmt = conn.prepareStatement("select * from natureza_declarante");
            rs = stmt.executeQuery();
            while (rs.next()){
                res.add(rs.getString("descricao"));
            }
        }
        catch (SQLException ex) {
        	ex.printStackTrace();
        }
        finally {
        Banco.closeConexao(conn, rs, stmt);
        return res;
        }
    }
    
    @SuppressWarnings("finally")
	public String selectId_Responsavel_Declaracao(String cpfcnpj) throws ClassNotFoundException, IOException {
        
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        String res = null;
        
        try {
            conn = Banco.getConexao();
            stmt = conn.prepareStatement("select id_responsavel_declaracao from declaracao_responsavel where id_declaracao = ?");
            stmt.setString(1, cpfcnpj);
            rs = stmt.executeQuery();
            while (rs.next()){
                res = rs.getString("id_responsavel_declaracao");
            }
        }
        catch (SQLException ex) {
        	ex.printStackTrace();
        }
        finally {
        Banco.closeConexao(conn, rs, stmt);
        return res;
        }
    }
    
    @SuppressWarnings("finally")
	public int deleteDeclaracao_Responsavel(String cpfcnpj) throws ClassNotFoundException, IOException {
        
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        int res = 0;
        
        try {
            conn = Banco.getConexao();
            String id = selectId_Responsavel_Declaracao(cpfcnpj);
            stmt = conn.prepareStatement("delete from declaracao_responsavel where id_declaracao = ?");            
            stmt.setString(1, cpfcnpj);
            stmt.executeUpdate();
            res = 1;
            deleteDeclaracao(cpfcnpj);
            f.deleteResponsavel_Declaracao(id);
        }
        catch (SQLException ex) {
        	res = 0;
        	ex.printStackTrace();        	
        }
        finally {
	        Banco.closeConexao(conn, rs, stmt);
	        System.out.println(res);
	        return res;
        }
    }
    
    @SuppressWarnings("finally")
	public int deleteDeclaracao(String cpfcnpj) throws ClassNotFoundException, IOException {
        
        Connection conn = null;
        PreparedStatement stmt = null;
        ResultSet rs = null;
        int res = 0;
        
        try {
            conn = Banco.getConexao();
            stmt = conn.prepareStatement("delete from declaracao where cpfcnpj = ?");            
            stmt.setString(1, cpfcnpj);
            stmt.executeUpdate();
            res = 1;
        }
        catch (SQLException ex) {
        	res = 0;
        	ex.printStackTrace();        	
        }
        finally {
	        Banco.closeConexao(conn, rs, stmt);
	        System.out.println(res);
	        return res;
        }
    }
    
    public static void main (String args[]) {
    	
    	FachadaDeclaracao f = new DeclaracaoDAO();
    	
    	try {
			Declaracao d = (Declaracao) f.selectDeclaracao("98174");
			System.out.println(d.getNatureza());
		} catch (ClassNotFoundException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} catch (IOException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}   	
    	
    }
    
}

